
const tools = require('../util/tool');
module.exports = class user_sql extends require('./mysql'){

    /**
     * 获取用户总表信息
     * @param currPage
     * @param pageNum
     */
    static gethouseListSql(currPage,pageNum){
        pageNum = Number(pageNum);
        currPage = Number(currPage);
        currPage = Number(currPage * pageNum);
        return new Promise((resolve , reject)=>{
            let sql = "select * from `house` order by createtime desc limit ?,?";
            console.log(sql);
            this.query(sql,this.formarParams(currPage,pageNum)).then(result=>{
                resolve(result)
            }).catch(err=>{
                reject(err)
            })
        })
    }
    static gethouseListTotal(){
        return new Promise((resolve, reject) => {
            let sql = "select count(1) as count from `house`"
            console.log(sql);
            this.query(sql).then(result=>{
                resolve(result)
            }).catch(err=>{
                reject(err)
            })
        })
    }

    /**
     * 根据id获取信息
     * @param id
     */
    static getDetailIdSql(id){
        return new Promise((resolve , reject)=>{
            let sql = "select * from `house` where id ="+ id;
            console.log(sql);
            this.query(sql).then(result=>{
                resolve(result)
            }).catch(err=>{
                reject(err)
            })
        })
    }
    


    /**
     * 单元删除
     * @param id
     */
    static delunitListSql(id){
        return new Promise((resolve, reject) => {
            let sql = 'delete from house where id ='+id;
            console.log(sql);
            this.query(sql).then(result=>{
                resolve('删除房屋信息成功')
            }).catch(err=>{
                reject('删除房屋信息失败')
            })
        })
    }

    /**
     * 房屋单元编辑
     * @param req
     * @param resp
     *
     */
    static updateunitListSql(u_id,communityname,unitname,unitnum,unitstatus,unitcontext){
        let currTime = tools.formatDate();
        return new Promise((resolve, reject) => {
            // let sql = 'UPDATE `house` SET communityname="'+communityname+'", unitname="'+unitname+'", unitnum="'+unitnum+'", unitstatus="'+unitstatus+'", unitcontext="'+unitcontext+'" WHERE id ='+u_id
            let sql = "update `house` set communityname='"+communityname+"',unitname='"+unitname+"',unitnum='"+unitnum+"',unitstatus='"+unitstatus+"',unitcontext='"+unitcontext+"' where id ="+u_id
            console.log(sql)
            this.query(sql).then(result=>{
                resolve('更新成功')
            }).catch(err=>{
                reject('更新失败')
            })
        })
    }

    /**
     * 更新用户住址
     * @param h_id
     * @param homeaddress
     * @returns {Promise<any>}
     */
    static updateUserHomeSql(h_id,homeaddress){
        return new Promise((resolve, reject) => {
            let sql = 'update `user` set homeaddress="'+homeaddress+'" where h_id ='+h_id;
            console.log(sql)
            this.query(sql).then(result=>{
                resolve('更新成功')
            }).catch(err=>{
                reject('更新失败')
            })
        })
    }

    static updateUserHidSql(id,h_id){
        return new Promise((resolve, reject) => {
            let sql = 'update `user` set h_id="'+h_id+'" where id ='+id;
            console.log(sql)
            this.query(sql).then(result=>{
                resolve('更新成功')
            }).catch(err=>{
                reject('更新失败')
            })
        })
    }

    /**
     * 房屋单元录入
     * @param req
     * @param resp
     *
     */
    static addunitListSql(id,communityname,unitname,unitnum,unitstatus,unitcontext,buildnum,storeynum, homenum,homename,homestatus,homemessage,homesuggestion,airnum,wifiname,washnum,smartstatus,homedesk,homechair,homeclothes,familystatus,createtime){

        return new Promise((resolve, reject) => {
            let sql = "insert into `house`(id,communityname,unitname,unitnum,unitstatus,unitcontext,buildnum,storeynum,homenum,homename,homestatus,homemessage,homesuggestion,airnum,wifiname,washnum,smartstatus,homedesk,homechair,homeclothes,familystatus,createtime) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            let createtime = tools.formatDate();
            // let value = [u_id,username,password,type,email,nickname,sex,address,note,createtime];
            console.log(sql)
            this.query(sql,this.formarParams(id,communityname,unitname,unitnum,unitstatus,unitcontext,buildnum,storeynum, homenum,homename,homestatus,homemessage,homesuggestion,airnum,wifiname,washnum,smartstatus,homedesk,homechair,homeclothes,familystatus,createtime)).then(result=>{
                resolve('添加成功')
            }).catch(err=>{
                reject('添加失败')
            })
        })
    }

    /**
     * 房屋单元表模糊查询
     */
    static searchListSql(communityname,unitname,createDt,status,currPage,pageNum){


        pageNum = Number(pageNum);
        currPage = Number(currPage);
        currPage = Number(pageNum * (currPage-1));
        return new Promise((resolve, reject)=>{
            // let sql="select * from `user` where username like '%"+username+"%' and type="+type+" limit ?,?"
            let sql="select * from `house` where 1=1";
            if (communityname) sql += " and communityname like '%"+communityname+"%'";
            if (unitname) sql += " and unitname like '%"+unitname+"%'";
            if (createDt) sql += " and createtime like '%"+createDt+"%'";
            if (status) sql += " and unitstatus= '" + status + "'";
            sql += " order by createtime desc limit ?,?";

            console.log(sql);
            this.query(sql,this.formarParams(currPage,pageNum)).then(result=>{
                console.log(this.formarParams(currPage,pageNum));
                resolve(result)
            }).catch(err=>{
                reject(err)
            })
        })
    }

    /**
     * 模糊查询分页
     * @param communityname
     * @param unitname
     * @param createDt
     * @param status
     * @returns {Promise<any>}
     */
    static searchListTotal(communityname,unitname,createDt,status){
        return new Promise((resolve, reject)=>{
            // let sql="select count(1) as count from `user` where username like '%"+username+"%' and type="+type+" "
            let sql="select count(1) as count from `house` where 1=1";
            if (communityname) sql += " and communityname like '%"+communityname+"%'";
            if (unitname) sql += " and unitname like '%"+unitname+"%'";
            if (createDt) sql += " and createtime like '%"+createDt+"%'";
            if (status) sql += " and unitstatus= '" + status + "'";
            console.log(sql);
            this.query(sql).then(result=>{
                resolve(result)
            }).catch(err=>{
                reject(err)
            })
        })
    }


    /**
     * 房号编辑
     * @param req
     * @param resp
     *
     */
    static updatenumListSql(id,unitname,buildnum,storeynum,homenum,homename,homestatus,homemessage,homesuggestion,airnum,wifiname,washnum,smartstatus,homedesk,homechair,homeclothes,familystatus){
        return new Promise((resolve, reject) => {
            let sql = 'update `house` set unitname="'+unitname+'", buildnum="'+buildnum+'", storeynum="'+storeynum+'", homenum="'+homenum+'", homename="'+homename+'", homestatus="'+homestatus+'", homemessage="'+homemessage+'" , homesuggestion="'+homesuggestion+'", airnum="'+airnum+'", wifiname="'+wifiname+'", washnum="'+washnum+'", smartstatus="'+smartstatus+'", homedesk="'+homedesk+'", homechair="'+homechair+'", homeclothes="'+homeclothes+'", familystatus="'+familystatus+'" where id ='+id;
            console.log(sql)
            this.query(sql).then(result=>{
                resolve('更新成功')
            }).catch(err=>{
                reject('更新失败')
            })
        })
    }


    /**
     * 房号搜索
     * @param homestatus
     * @param unitname
     * @param buildnum
     * @param homenum
     * @param homename
     * @param currPage
     * @param pageNum
     * @returns {Promise<any>}
     */
    static searchnumListSql(homestatus,unitname,buildnum,homenum,homename,currPage,pageNum){
        pageNum = Number(pageNum);
        currPage = Number(currPage);
        currPage = Number(pageNum * currPage);
        return new Promise((resolve, reject)=>{
            let sql="select * from `house` where 1=1";
            if (homestatus) sql += " and homestatus= '" + homestatus + "'";
            if (unitname) sql += " and unitname like '%"+unitname+"%'";
            if (buildnum) sql += " and buildnum like '%"+buildnum+"%'";
            if (homenum) sql += " and homenum like '%"+homenum+"%'";
            if (homename) sql += " and homename like '%"+homename+"%'";
            sql += " order by createtime desc limit ?,?";

            console.log(sql);
            this.query(sql,this.formarParams(currPage,pageNum)).then(result=>{
                console.log(this.formarParams(currPage,pageNum));
                resolve(result)
            }).catch(err=>{
                reject(err)
            })
        })
    }

    /**
     * 房号模糊查询分页
     * @param homestatus
     * @param unitname
     * @param buildnum
     * @param homenum
     * @param homename
     * @returns {Promise<any>}
     */
    static searchnumListTotal(homestatus,unitname,buildnum,homenum,homename){
        return new Promise((resolve, reject)=>{
            // let sql="select count(1) as count from `user` where username like '%"+username+"%' and type="+type+" "
            let sql="select count(1) as count from `house` where 1=1";
            if (homestatus) sql += " and homestatus= '" + homestatus + "'";
            if (unitname) sql += " and unitname like '%"+unitname+"%'";
            if (buildnum) sql += " and buildnum like '%"+buildnum+"%'";
            if (homenum) sql += " and homenum like '%"+homenum+"%'";
            if (homename) sql += " and homename like '%"+homename+"%'";
            console.log(sql);
            this.query(sql).then(result=>{
                resolve(result)
            }).catch(err=>{
                reject(err)
            })
        })
    }

    /**
     * 房号批量删除
     * @param ids
     */
    static delnumListSql(ids){
        return new Promise((resolve, reject) => {
            let sql = `delete from house where id in (${ids})` ; // in 是用来做批量删除的写法
            // let sql = 'delete from house where id in (id)'
            console.log(sql);
            this.query(sql).then(result=>{
                resolve('批量删除房屋信息成功')
            }).catch(err=>{
                reject('批量删除房屋信息失败')
            })
        })
    }

    /**
     * 获取用户自己的房号信息
     * @param h_id
     * @param currPage
     * @param pageNum
     * @returns {Promise<any>}
     */
    static getHouseDataSql(id,currPage,pageNum){
        pageNum = Number(pageNum);
        currPage = Number(currPage);
        currPage = Number(currPage * pageNum);
        return new Promise((resolve, reject) => {
            let sql = "select * from `house` where id ='"+id+"' limit ?,?";
            console.log(sql)
            this.query(sql,this.formarParams(currPage,pageNum)).then(result=>{
                resolve(result)
            }).catch(err=>{
                reject(err)
            })
        })
    }
    static getHouseDataTotal(id){
        return new Promise((resolve, reject) => {
            let sql = "select count(1) as count from `house` where id= "+id;
            console.log(sql)
            this.query(sql).then(result=>{
                resolve(result)
            }).catch(err=>{
                reject(err)
            })
        })
    }


    /**
     * 智能设备状态改变
     * @param req
     * @param resp
     *
     */
    static updatehomeStatusSql(id,smartstatus){
        return new Promise((resolve, reject) => {
            let sql = 'update `house` set smartstatus="'+smartstatus+'" where id ='+id;
            console.log(sql)
            this.query(sql).then(result=>{
                resolve('改变成功')
            }).catch(err=>{
                reject('改变失败')
            })
        })
    }

    /**
     * 家居设备状态改变
     * @param req
     * @param resp
     *
     */
    static updatefamilyStatusSql(id,familystatus){
        return new Promise((resolve, reject) => {
            let sql = 'update `house` set familystatus="'+familystatus+'" where id ='+id;
            console.log(sql)
            this.query(sql).then(result=>{
                resolve('改变成功')
            }).catch(err=>{
                reject('改变失败')
            })
        })
    }


    /**
     * 设备列表查询
     * @param smartstatus
     * @param homenum
     * @param startDate
     * @param endDate
     * @param familystatus
     * @param currPage
     * @param pageNum
     * @returns {Promise<any>}
     */
    static searchdeviceListSql(smartstatus,homenum,startDate,endDate,familystatus,currPage,pageNum){
        pageNum = Number(pageNum);
        currPage = Number(currPage);
        currPage = Number(pageNum * currPage);
        return new Promise((resolve, reject)=>{
            // let sql="select * from `user` where username like '%"+username+"%' and type="+type+" limit ?,?"
            let sql="select * from `house` where 1=1";
            if (smartstatus) sql += " and smartstatus= '" + smartstatus + "'";
            if (homenum) sql += " and homenum like '%"+homenum+"%'";
            if (startDate && endDate) sql += " and (createtime BETWEEN '"+startDate+"' AND '"+endDate+"')";
            if (familystatus) sql += " and familystatus= '" + familystatus + "'";
            sql += " limit ?,?";

            console.log(sql);
            this.query(sql,this.formarParams(currPage,pageNum)).then(result=>{
                console.log(this.formarParams(currPage,pageNum));
                resolve(result)
            }).catch(err=>{
                reject(err)
            })
        })
    }

    /**
     * 设备列表分页
     * @param smartstatus
     * @param homenum
     * @param startDate
     * @param endDate
     * @param familystatus
     * @returns {Promise<any>}
     */
    static searchdeviceListTotal(smartstatus,homenum,startDate,endDate,familystatus){
        return new Promise((resolve, reject)=>{
            // let sql="select count(1) as count from `user` where username like '%"+username+"%' and type="+type+" "
            let sql='select count(1) as count from `house` where 1=1';
            if (smartstatus) sql += " and smartstatus= '" + smartstatus + "'";
            if (homenum) sql += " and homenum like '%"+homenum+"%'";
            if (startDate && endDate) sql += " and (createtime BETWEEN '"+startDate+"' AND '"+endDate+"')";
            if (familystatus) sql += " and familystatus= '" + familystatus + "'";
            console.log(sql);
            this.query(sql).then(result=>{
                resolve(result)
            }).catch(err=>{
                reject(err)
            })
        })
    }

}
    
